﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;
using System.Collections;

namespace ImpOracleFromExcel
{
    public partial class FrmMain : Form
    {
        #region 属性定义

        private List<ImportEntity> columns_Excel = new List<ImportEntity>();
        private string sheetName;
        private OracleHelper oh;
        private OleDBHelper oleh;
        private string table = string.Empty;
        /// <summary>
        /// 要导入的数据条数
        /// </summary>
        private int dataCount = 0;
        /// <summary>
        /// 已导入的数据条数
        /// </summary>
        private int impCount = 0;

        private Hashtable oracle_columns = new Hashtable();

        #endregion

        #region 初始化

        public FrmMain()
        {
            InitializeComponent();
            LogMsg("欢迎使用数据导入小工具，版权所有 @孔凡智");
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            // 初始化支持的数据类型
            InitType();
        }

        /// <summary>
        /// 初始化支持的数据类型
        /// </summary>
        private void InitType()
        {
            LogMsg("正在初始化支持的数据类型...");

            try
            {
                foreach (var v in typeof(OracleType).GetFields())
                {
                    if (v.FieldType.IsEnum == true)
                    {
                        cbType.Items.Add(v.Name);
                    }
                }
                cbType.SelectedItem = OracleType.NVarChar.ToString();
                LogMsg("支持的数据类型初始化已完毕。");
            }
            catch (Exception exc)
            {
                LogMsg(exc.ToString());
            }
        }

        private void txtMsg_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.A && e.Control)
            {
                txtMsg.SelectAll();
            }
        }

        private void tsmiClear_Click(object sender, EventArgs e)
        {
            txtMsg.Clear();
            LogMsg("欢迎使用数据导入小工具，版权所有 @孔凡智");
        }

        private delegate void InvokeButtonState(bool IsEnable);
        private void ChangeButtonState(bool IsEnable)
        {
            if (this.InvokeRequired)
            {
                InvokeButtonState _invoke = new InvokeButtonState(ChangeButtonState);
                this.Invoke(_invoke, new object[] { IsEnable });
            }
            else
            {
                //btnConfrm.Enabled = IsEnable;
                //btnConnOracle.Enabled = IsEnable;
                //btnImport.Enabled = IsEnable;
                //btnConnectODBC.Enabled = IsEnable;
                //btnSelectExcel.Enabled = IsEnable;
                //cbTableOracle.Enabled = IsEnable;
                //cbFieldExcel.Enabled = IsEnable;
                //cbFieldOracle.Enabled = IsEnable;
                //cbType.Enabled = IsEnable;
                //txtDBOracle.ReadOnly = !IsEnable;
                //txtUserOracle.ReadOnly = !IsEnable;
                //txtPwdOracle.ReadOnly = !IsEnable;
                tabControl1.Enabled = IsEnable;
                btnImport.Enabled = IsEnable;
            }
        }

        #region 消息记录 LogMsg

        private delegate void InvokeMsg(string msg);

        /// <summary>
        /// 将消息输出到界面
        /// </summary>
        /// <param name="msg"></param>
        public void LogMsg(string msg)
        {
            if (txtMsg.InvokeRequired)
            {
                InvokeMsg im = new InvokeMsg(LogMsg);
                this.Invoke(im, new object[] { msg });
            }
            else
            {
                txtMsg.AppendText(">> " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ---> " + msg + "\n");
            }
        }

        #endregion

        #endregion

        #region Excel操作

        private void btnSelectExcel_Click(object sender, EventArgs e)
        {
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtExcel.Text = ofd.FileName;
                LogMsg("已选择数据源为:" + ofd.FileName);
                btnConnectODBC_Click(null, null);
            }
            else
            {
                txtExcel.Text = null;
                LogMsg("未选择任何数据源，要进行数据导入，请选择数据源.");
                lbSheetNames.Items.Clear();
            }
        }

        private void btnConnectODBC_Click(object sender, EventArgs e)
        {
            LogMsg("正在连接到数据源...");

            if (string.IsNullOrEmpty(txtExcel.Text))
            {
                LogMsg("请选择数据源文件...");
                return;
            }

            oleh = new OleDBHelper(txtExcel.Text, this);
            List<string> sheets = oleh.GetExcelSheets();
            lbSheetNames.Items.Clear();
            foreach (string s in sheets)
            {
                lbSheetNames.Items.Add(s);
            }
        }

        private void lbSheetNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            //if (lbSheetNames.SelectedIndex < 0)
            //{
            //    return;
            //}

            //sheetName = lbSheetNames.SelectedItem.ToString();
            if (bw.IsBusy)
            {
                LogMsg("正忙...");
                return;
            }
            else
            {
                bw.RunWorkerAsync();
            }
        }

        #region 查询Excel中数据

        private delegate string InvokeSheetName();

        private string GetSheetName()
        {
            if (this.InvokeRequired)
            {
                InvokeSheetName _invoke = new InvokeSheetName(GetSheetName);
                object obj = this.Invoke(_invoke, null);
                return obj.ToString();
            }
            else
            {
                return lbSheetNames.SelectedItem.ToString();
            }
        }

        public delegate void MyInvoke(DataTable str);

        private void bw_DoWork(object sender, DoWorkEventArgs e)
        {
            sheetName = GetSheetName();

            if (string.IsNullOrEmpty(sheetName))
            {
                LogMsg("sheetName为空;");
                return;
            }

            LogMsg("正在查询" + sheetName + "中数据...");
            ChangeButtonState(false);

            string sql = "select * from [" + sheetName + "]";
            DataTable dt = oleh.GetSheetData(sql);

            columns_Excel.Clear();
            foreach (DataColumn dc in dt.Columns)
            {
                ImportEntity ie = new ImportEntity();
                ie.ColumnExcel = dc.ColumnName;
                columns_Excel.Add(ie);
            }

            BindingData(dt);
        }

        private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            LogMsg("数据查询完毕,总共:" + dataCount + "条数据.");
            ChangeButtonState(true);
        }

        private void BindingData(DataTable data)
        {
            dataCount = data.Rows.Count;
            if (gvData.InvokeRequired)
            {
                MyInvoke _myInvoke = new MyInvoke(BindingData);
                this.Invoke(_myInvoke, new object[] { data });
            }
            else
            {
                gvData.DataSource = data;
            }
        }

        #endregion

        #endregion

        #region 连接Oracle并查询表结构

        private void btnConnOracle_Click(object sender, EventArgs e)
        {
            cbTableOracle.Items.Clear();
            if (!bwConnOracle.IsBusy)
            {
                bwConnOracle.RunWorkerAsync();
            }
            else
            {
                LogMsg("正忙...");
            }
        }

        private void bwConnOracle_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                LogMsg("正在连接Oracle...");
                ChangeButtonState(false);

                oh = new OracleHelper(txtDBOracle.Text,
                    txtUserOracle.Text, txtPwdOracle.Text, this);
                List<string> tableList = oh.GetTables();
                BindingOracleTableList(tableList);
                LogMsg("成功连接至Oracle，并成功获取表结构");
            }
            catch (Exception exc)
            {
                LogMsg("连接Oracle失败,请检查原因.");
                LogMsg(exc.ToString());
            }
        }

        private void bwConnOracle_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            ChangeButtonState(true);
        }

        private delegate void InvokeOracleTable(List<string> tablelist);

        private void BindingOracleTableList(List<string> tablelist)
        {
            if (this.InvokeRequired)
            {
                InvokeOracleTable _invoke = new InvokeOracleTable(BindingOracleTableList);
                this.Invoke(_invoke, new object[] { tablelist });
            }
            else
            {
                cbTableOracle.Items.Clear();
                foreach (string s in tablelist)
                {
                    cbTableOracle.Items.Add(s);
                }
            }
        }

        #endregion

        #region 获取Oracle列数据

        private void cbTableOracle_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbFieldOracle.Items.Clear();

            columns_Excel.RemoveAll(x => x.IsAdd == true);

            Refreshdata();

            if (cbTableOracle.SelectedItem != null)
            {
                table = cbTableOracle.SelectedItem.ToString();
                if (!bwGetOracleColumn.IsBusy)
                {
                    bwGetOracleColumn.RunWorkerAsync();
                }
                else
                {
                    LogMsg("正忙...");
                }
            }
            else
            {
                LogMsg("未选中任何信息.");
            }


        }

        private void bwGetOracleColumn_DoWork(object sender, DoWorkEventArgs e)
        {
            ChangeButtonState(false);

            LogMsg("正在获取列信息...");

            try
            {
                oh.GetColumns(table, oracle_columns);

                BindingOracleColumnList();

                LogMsg("成功获取列信息.");
            }
            catch (Exception exc)
            {
                LogMsg(exc.ToString());
            }
        }

        private void bwGetOracleColumn_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            ChangeButtonState(true);
        }

        private delegate void InvokeOracleColumn();

        private void BindingOracleColumnList()
        {
            if (this.InvokeRequired)
            {
                InvokeOracleColumn _invoke = new InvokeOracleColumn(BindingOracleColumnList);
                this.Invoke(_invoke);
            }
            else
            {
                cbFieldOracle.Items.Clear();
                lock (oracle_columns)
                {
                    foreach (string key in oracle_columns.Keys)
                    {
                        cbFieldOracle.Items.Add(key);
                    }
                }
            }
        }

        #endregion

        #region Excel列与Oracle列匹配

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedTab == tabPage2)
            {
                Refreshdata();

                cbFieldExcel.Items.Clear();

                foreach (ImportEntity ie in columns_Excel)
                {
                    if (!ie.IsAdd)
                    {
                        cbFieldExcel.Items.Add(ie.ColumnExcel);
                    }
                }
            }
        }

        private void Refreshdata()
        {
            lbData.Items.Clear();
            foreach (ImportEntity ie in columns_Excel)
            {
                string temp = ie.ColumnExcel + ie.ColumnBJ + ie.ColumnOracle + ":" + ie.FieldType.ToString();
                lbData.Items.Add(temp);
            }
        }

        private void btnConfrm_Click(object sender, EventArgs e)
        {
            try
            {
                if (cbFieldExcel.SelectedItem == null)
                {
                    if (cbFieldOracle.SelectedItem != null)
                    {
                        if (!string.IsNullOrEmpty(cbFieldExcel.Text))
                        {
                            bool isAdd = false;
                            foreach (ImportEntity ie in columns_Excel)
                            {
                                if (ie.ColumnOracle == cbFieldOracle.Text)
                                {
                                    isAdd = true;
                                    ie.ColumnOracle = cbFieldOracle.SelectedItem.ToString();
                                    ie.FieldType = (OracleType)Enum.Parse(typeof(OracleType), cbType.SelectedItem.ToString(), false);
                                    ie.ColumnExcel = cbFieldExcel.Text;
                                    ie.IsAdd = true;
                                    break;
                                }
                            }

                            if (!isAdd)
                            {
                                ImportEntity ie = new ImportEntity();
                                ie.ColumnOracle = cbFieldOracle.SelectedItem.ToString();
                                ie.FieldType = (OracleType)Enum.Parse(typeof(OracleType), cbType.SelectedItem.ToString(), false);
                                ie.ColumnExcel = cbFieldExcel.Text;
                                ie.IsAdd = true;
                                columns_Excel.Add(ie);
                            }
                        }
                        else
                        {
                            foreach (ImportEntity ie in columns_Excel)
                            {
                                if (ie.ColumnOracle == cbFieldOracle.Text)
                                {
                                    if (ie.IsAdd == true)
                                    {
                                        columns_Excel.Remove(ie);
                                    }
                                    else
                                    {
                                        ie.ColumnOracle = cbFieldOracle.SelectedItem.ToString();
                                        ie.FieldType = (OracleType)Enum.Parse(typeof(OracleType), cbType.SelectedItem.ToString(), false);
                                        ie.ColumnExcel = null;
                                    }
                                    break;
                                }
                            }
                        }
                    }
                }
                else
                {
                    foreach (ImportEntity ie in columns_Excel)
                    {
                        if (ie.ColumnExcel == cbFieldExcel.SelectedItem.ToString())
                        {
                            ie.ColumnOracle = cbFieldOracle.SelectedItem.ToString();
                            ie.FieldType = (OracleType)Enum.Parse(typeof(OracleType), cbType.SelectedItem.ToString(), false);
                        }
                    }
                }

                Refreshdata();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());
            }
        }

        private void lbData_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (lbData.SelectedIndex < 0)
                {
                    return;
                }

                string temp = lbData.SelectedItem.ToString();
                string strColumnExcel = temp.Split('<')[0];
                foreach (ImportEntity ie in columns_Excel)
                {
                    if (ie.ColumnExcel == strColumnExcel)
                    {
                        cbFieldExcel.SelectedItem = ie.ColumnExcel;

                        if (cbFieldExcel.SelectedItem == null)
                        {
                            cbFieldExcel.Text = ie.ColumnExcel;
                        }

                        cbFieldOracle.SelectedItem = ie.ColumnOracle;
                        cbType.SelectedItem = ie.FieldType.ToString();
                        break;
                    }
                }
            }
            catch (Exception exc)
            {
                LogMsg(exc.ToString());
            }
        }

        private void cbFieldOracle_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbFieldOracle.SelectedIndex > -1)
            {
                try
                {
                    cbType.SelectedItem = oracle_columns[cbFieldOracle.SelectedItem].ToString();
                }
                catch (Exception exc)
                {
                    LogMsg(exc.ToString());
                    cbType.SelectedItem = OracleType.VarChar;
                }
            }
            else
            {
                cbType.SelectedItem = OracleType.VarChar;
            }
        }

        #endregion

        #region 数据导入

        private void btnImport_Click(object sender, EventArgs e)
        {
            bwImport.RunWorkerAsync();
        }

        private void bwImport_DoWork(object sender, DoWorkEventArgs e)
        {
            LogMsg("正在导入数据,请稍后...");
            ChangeButtonState(false);
            impCount = 0;
            ImportData();
        }

        private void bwImport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            LogMsg("应导入数据:" + dataCount.ToString() + "条," + "实际导入数据:" + impCount.ToString() + "条");
            if (impCount == dataCount)
            {
                LogMsg("数据导入完成.");
            }
            else
            {
                int i = dataCount - impCount;
                LogMsg("有" + i.ToString() + "条数据未成功导入,请查找原因.");
            }
            ChangeButtonState(true);
        }

        private void ImportData()
        {
            DataTable dt = gvData.DataSource as DataTable;

            string sql = BuildSQL();

            if (string.IsNullOrEmpty(sql))
            {
                return;
            }
            try
            {
                foreach (DataRow dr in dt.Rows)
                {

                    List<ImportEntity> impList = columns_Excel;

                    impList.RemoveAll(x => string.IsNullOrEmpty(x.ColumnOracle));

                    OracleParameter[] ops = new OracleParameter[impList.Count];
                    for (int i = 0; i < impList.Count; i++)
                    {
                        ops[i] = new OracleParameter(impList[i].ColumnOracle, impList[i].FieldType);
                        ops[i].Value = dr[impList[i].ColumnExcel];
                    }

                    oh.ExecuteNonQuery(sql, ops);

                    impCount = impCount + 1;
                }
            }
            catch (Exception exc)
            {
                LogMsg(exc.ToString());
            }
        }

        private string BuildSQL()
        {
            if (string.IsNullOrEmpty(table))
            {
                return null;
            }

            string sql = "insert into " + table + " (";
            int count = columns_Excel.Count;
            for (int i = 0; i < count; i++)
            {
                string temp = columns_Excel[i].ColumnOracle;
                if (!string.IsNullOrEmpty(temp))
                {
                    sql = sql + columns_Excel[i].ColumnOracle + ",";
                }
            }

            if (sql.EndsWith(","))
            {
                sql = sql.Remove(sql.Length - 1);
            }

            sql = sql + ") values (";

            for (int i = 0; i < count; i++)
            {
                if (i < count - 1)
                {
                    sql = sql + ":" + columns_Excel[i].ColumnOracle + ",";
                }
                else
                {
                    sql = sql + ":" + columns_Excel[i].ColumnOracle;
                }
            }

            sql = sql + ")";

            return sql;
        }

        private ImportEntity GetEntityFromList(string str)
        {
            foreach (ImportEntity ie in columns_Excel)
            {
                if (ie.ColumnExcel == str)
                {
                    return ie;
                }
            }
            return null;
        }

        #endregion
    }
}
